package main.model;

/**
 * Created by hdt3213 on 17/3/30.
 */

import java.sql.*;
import java.util.ArrayList;

public class DB {
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://127.0.0.1:3306/";
	private static final String db = "chat_server";
	private static final String user = "root";
	private static final String password = "248536";

	private Connection connection;

	PreparedStatement insertUserStatement;
	PreparedStatement updateUserStatement;
	PreparedStatement deleteUserStatement;
	PreparedStatement userIdQuery;
	PreparedStatement userUuidQuery;


	PreparedStatement insertGroupStatement;
	PreparedStatement updateGroupStatement;
	PreparedStatement deleteGroupStatement;
	PreparedStatement groupIdQuery;
	PreparedStatement groupUuidQuery;

	PreparedStatement joinStatement;
	PreparedStatement quitStatement;
	PreparedStatement assignStatement;
	PreparedStatement roleQuery;
	PreparedStatement memberQuery;

	public void prepare() throws SQLException {
		String sql;

		sql = "insert into User (uuid, name, password, face) values (?, ?, ?, ?);";
		insertUserStatement = connection.prepareStatement(sql);

		sql = "update User set name=?, password=?, face=? where id=?";
		updateUserStatement =  connection.prepareStatement(sql);

		sql = "delete from User where id=?";
		deleteUserStatement = connection.prepareStatement(sql);

		sql = "select uuid, name, password,face from User where id=?";
		userIdQuery = connection.prepareStatement(sql);

		sql = "select id, name, password, face from User where uuid=?";
		userUuidQuery = connection.prepareStatement(sql);

		sql = "insert into UserGroup (uuid, name, face) values (?, ?, ?);";
		insertGroupStatement = connection.prepareStatement(sql);

		sql = "update UserGroup set name=?, face=? where id=?";
		updateGroupStatement =  connection.prepareStatement(sql);

		sql = "delete from UserGroup where id=?";
		deleteGroupStatement = connection.prepareStatement(sql);

		sql = "select uuid, name, face from UserGroup where id=?";
		groupIdQuery = connection.prepareStatement(sql);

		sql = "select id, name from UserGroup where uuid=?";
		groupUuidQuery = connection.prepareStatement(sql);

		sql = "insert into GroupMember (uid, gid, role) values (?, ?, ?);";
		joinStatement= connection.prepareStatement(sql);

		sql = "delete from GroupMember where uid=? and gid=?;";
		quitStatement = connection.prepareStatement(sql);


		sql = "update GroupMember set role=? where uid=? and gid=?";
		assignStatement = connection.prepareStatement(sql);

		sql = "select role from GropMember where uid=? and gid=?";
		roleQuery = connection.prepareStatement(sql);

		sql = "select uid from GropMember where gid=?";
		memberQuery = connection.prepareStatement(sql);

	}


	public String insertUser(User user) throws SQLException {
		String uuid = DB.getUUID(this);
		insertUserStatement.setString(1, uuid);
		insertUserStatement.setString(2, user.getName());
		insertUserStatement.setString(3, user.getPassword());
		insertUserStatement.setString(4, String.valueOf(user.getFace()));
		insertUserStatement.execute();
		user.setReserved(true);
		user.setUuid(uuid);
		return uuid;
	}


	public void deleteUser(User user) throws SQLException {
		deleteUserStatement.setString(1, String.valueOf(user.getId()));
		deleteUserStatement.execute();
		user.setReserved(false);
	}

	public void updateUser(User user) throws SQLException {
		updateUserStatement.setString(1, user.getName());
		updateUserStatement.setString(2, user.getPassword());
		updateUserStatement.setString(3, String.valueOf(user.getFace()));
		updateUserStatement.setString(4, String.valueOf(user.getId()));
		updateUserStatement.execute();
		user.setReserved(true);
	}

	public User getUser(int id) throws SQLException {
		User user = new User();
		userIdQuery.setString(1, String.valueOf(id));
		ResultSet result = userIdQuery.executeQuery();

		if (result.next()) {
			user.setId(id);
			user.setUuid(result.getString("uuid"));
			user.setName(result.getString("name"));
			user.setPassword(result.getString("password"));
			user.setFace(result.getInt("face"));
			user.setReserved(true);
			return user;
		}
		return null;
	}

	public User getUser(String uuid) throws SQLException {
		User user = new User();

		userUuidQuery.setString(1, String.valueOf(uuid));
		ResultSet result = userUuidQuery.executeQuery();

		if (result.next()) {
			user.setId(result.getInt("id"));
			user.setUuid(uuid);
			user.setName(result.getString("name"));
			user.setPassword(result.getString("password"));
			user.setFace(result.getInt("face"));
			user.setReserved(true);
			return user;
		}
		return null;
	}

	public String insertGroup(Group group) throws SQLException {
		String uuid = DB.getUUID(this);
		insertGroupStatement.setString(1, uuid);
		insertGroupStatement.setString(2, group.getName());
		insertGroupStatement.setString(3, String.valueOf(group.getFace()));
		insertGroupStatement.execute();
		group.setReserved(true);
		return uuid;
	}

	public void deleteGroup(Group group) throws SQLException {
		deleteGroupStatement.setString(1, String.valueOf(group.getId()));
		deleteGroupStatement.execute();
		group.setReserved(false);
	}

	public void updateGroup(Group group) throws SQLException {
		updateGroupStatement.setString(1, group.getName());
		updateGroupStatement.setString(2, String.valueOf(group.getFace()));
		updateGroupStatement.setString(3, String.valueOf(group.getId()));
		updateGroupStatement.execute();
		group.setReserved(true);
	}

	public Group getGroup(int id) throws SQLException {
		Group group = new Group();

		groupIdQuery.setString(1, String.valueOf(id));
		ResultSet result = groupIdQuery.executeQuery();

		if (result.next()) {
			group.setId(id);
			group.setUuid(result.getString("uuid"));
			group.setName(result.getString("name"));
			group.setFace(result.getInt("face"));
			group.setReserved(true);
			return group;
		}
		return null;
	}

	public Group getGroup(String uuid) throws SQLException {
		Group group = new Group();

		groupUuidQuery.setString(1, uuid);
		ResultSet result = groupUuidQuery.executeQuery();

		if (result.next()) {
			group.setId(result.getInt("id"));
			group.setUuid(uuid);
			group.setName(result.getString("name"));
			group.setFace(result.getInt("face"));
			group.setReserved(true);
			return group;
		}
		return null;
	}

	public void join(String uid, String gid, int role) throws SQLException {
		User user = getUser(uid);
		Group group = getGroup(gid);
		joinStatement.setString(1, String.valueOf(user.getId()));
		joinStatement.setString(2, String.valueOf(group.getId()));
		joinStatement.setString(3, String.valueOf(role));
		joinStatement.execute();
	}

	public void quit(String uid, String gid) throws SQLException {
		User user = getUser(uid);
		Group group = getGroup(gid);
		quitStatement.setString(1, String.valueOf(user.getId()));
		quitStatement.setString(2, String.valueOf(group.getId()));
		quitStatement.execute();
	}

	public void assign(String uid, String gid, int role) throws SQLException {
		User user = getUser(uid);
		Group group = getGroup(gid);
		assignStatement.setString(1, String.valueOf(role));
		assignStatement.setString(2, String.valueOf(user.getId()));
		assignStatement.setString(3, String.valueOf(group.getId()));
		assignStatement.execute();
	}

	public String createGroup(String creator, String name, int face) throws SQLException {
		Group group = new Group();
		group.setName(name);
		group.setFace(face);
		String gid = insertGroup(group);
		join(creator, gid, Group.creator);
		return gid;
	}

	public int getRole(String uid, String gid) throws SQLException {
		roleQuery.setString(1, uid);
		roleQuery.setString(2, gid);
		ResultSet result = groupUuidQuery.executeQuery();

		if (result.next()) {
			return result.getInt("role");
		}
		return 0;
	}

	public String[] getMembers(String gid) throws SQLException {
		memberQuery.setString(1, gid);
		ResultSet result = groupUuidQuery.executeQuery();
		ArrayList<String> users = new ArrayList<String>();

		if (result.next()) {
			users.add(result.getString("uid"));
		}
		return (String[])users.toArray();
	}

	public void connect() throws SQLException, ClassNotFoundException {
		Class.forName(driver);
		connection = DriverManager.getConnection(url+db, user, password);
	}

	public static void main(String[] args) {

	}

	public static String getUUID(Object obj) {
		String first = String.valueOf(obj.hashCode() % 10000);
		String second = String.valueOf(System.currentTimeMillis() % 10000);
		String third = String.valueOf((int) (Math.random() * 1000));
		return first + second + third;
	}

}
